﻿using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using System;
using System.Collections.Generic;
using System.IO;
using System.Reflection;
using System.Text;
using System.Web;

namespace Infrastructure
{
    public class ExcelHepler<TModel>
    {
        //测试代码1  
        /*      BoBase<HouseType, HouseType> bll = new BoBase<HouseType, HouseType>(); 
         List<HouseType> list=new List<HouseType> (); 
         list.AddRange(bll.GetList(string.Empty)); 
         Dictionary<string, string> FieldNames=new Dictionary<string,string> (); 
         FieldNames.Add("HT_Code","宿舍类型编码"); 
          FieldNames.Add("HT_ActiveFlag","状态"); 
          FieldNames.Add("HT_Name","宿舍类型名称"); 

          ExportToFile<HouseType>.Export(list, "测试", Path.Combine(AppDomain.CurrentDomain.BaseDirectory, DateTime.Now.ToString()+".xls"), FieldNames); 
         * */

        //测试代码2  
        /*   BoBase<SurveyPerformance, SurveyPerformance> bl2l = new BoBase<SurveyPerformance, SurveyPerformance>(); 
          List<SurveyPerformance> list2 = new List<SurveyPerformance>(); 
           list2.AddRange(bl2l.GetList(string.Empty)); 
           Dictionary<string, string> FieldNames2 = new Dictionary<string, string>(); 
           FieldNames2.Add("SP_Code", "级别编码"); 
           FieldNames2.Add("SP_Name", "级别名称"); 
           FieldNames2.Add("SP_Status", "级别状态"); 
           FieldNames2.Add("SP_ID", "编号"); 
           ExportToFile<SurveyPerformance>.Export(list2, "测试", Path.Combine(AppDomain.CurrentDomain.BaseDirectory, DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls"), FieldNames2); 
         *  * */

        /// <summary>     
        /// DataTable导出到Excel文件     
        /// </summary>     
        /// <param name="dataSource">源DataTable</param>     
        /// <param name="headerText">表头文本</param>     
        /// <param name="saveFileName">保存位置</param>     
        /// <param name="fieldNames">表字段</param>      
        public static void Export(List<TModel> dataSource, string headerText, string saveFileName, Dictionary<string, string> fieldNames)
        {
            using (MemoryStream ms = Export(dataSource, headerText, fieldNames))
            {
                using (FileStream fs = new FileStream(saveFileName, FileMode.Create, FileAccess.Write))
                {
                    byte[] data = ms.ToArray();
                    fs.Write(data, 0, data.Length);
                    fs.Flush();
                }
            }
        }

        /// <summary>     
        /// 用于Web导出     
        /// </summary>     
        /// <param name="dataSource">源DataTable</param>     
        /// <param name="headerText">表头文本</param>     
        /// <param name="saveFileName">文件名</param>     
        /// <param name="fieldNames">表字段</param>      
        public static void ExportByWeb(List<TModel> dataSource, string headerText, string saveFileName, Dictionary<string, string> fieldNames)
        {
            HttpContext curContext = HttpContext.Current;
            // 设置编码和附件格式     
            curContext.Response.ContentType = "application/vnd.ms-excel";
            curContext.Response.ContentEncoding = Encoding.UTF8;
            curContext.Response.Charset = "";
            curContext.Response.AppendHeader("Content-Disposition",
                "attachment;filename=" + HttpUtility.UrlEncode(saveFileName, Encoding.UTF8));
            curContext.Response.BinaryWrite(Export(dataSource, headerText, fieldNames).GetBuffer());
            curContext.Response.End();
        }

        private static MemoryStream Export(List<TModel> list, string strHeaderText, Dictionary<string, string> FieldNames)
        {
            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet();
            HSSFCellStyle dateStyle = (HSSFCellStyle)workbook.CreateCellStyle();
            HSSFDataFormat format = (HSSFDataFormat)workbook.CreateDataFormat();
            dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");

            //取得列宽     
            int[] arrColWidth = new int[FieldNames.Count];
            int count = 0;
            foreach (KeyValuePair<string, string> item in FieldNames)
            {
                arrColWidth[count] = Encoding.GetEncoding(936).GetBytes(item.Value).Length;
                count++;
            }

            int rowIndex = 0;
            HSSFRow dataRow;
            HSSFCell newCell;
            PropertyInfo pinfo;
            foreach (TModel row in list)
            {
                #region 新建表，填充表头，填充列头，样式  
                if (rowIndex == 65535 || rowIndex == 0)
                {
                    if (rowIndex != 0)
                    {
                        sheet = (HSSFSheet)workbook.CreateSheet();
                    }

                    #region 表头及样式                     
                    AddFirstRow(workbook, sheet, FieldNames, strHeaderText);
                    #endregion
                    #region 列头及样式                      
                    AddSecondRow(workbook, sheet, FieldNames, arrColWidth);
                    #endregion
                    rowIndex = 2;
                }
                #endregion

                #region 填充内容  
                dataRow = (HSSFRow)sheet.CreateRow(rowIndex);
                count = 0;
                foreach (KeyValuePair<string, string> column in FieldNames)
                {
                    newCell = (HSSFCell)dataRow.CreateCell(count);
                    pinfo = typeof(TModel).GetProperty(column.Key);
                    string drValue = "";
                    string dateytype = "";
                    if (pinfo != null)
                    {
                        drValue = pinfo.GetValue(row, null) == null ? "" : pinfo.GetValue(row, null).ToString();
                        dateytype = pinfo.PropertyType.ToString();
                    }

                    switch (dateytype)
                    {
                        case "System.String"://字符串类型     
                            newCell.SetCellValue(drValue);
                            break;
                        case "System.DateTime"://日期类型     
                            DateTime dateV;
                            DateTime.TryParse(drValue, out dateV);
                            newCell.SetCellValue(dateV);
                            newCell.CellStyle = dateStyle;//格式化显示     
                            break;
                        case "System.Boolean"://布尔型     
                                              //bool boolV = false;
                                              //bool.TryParse(drValue, out boolV);
                                              //newCell.SetCellValue(boolV);
                                              //break;
                        case "System.Int16"://整型     
                        case "System.Int32":
                        case "System.Int64":
                        case "System.Byte":
                            int intV = 0;
                            int.TryParse(drValue, out intV);
                            newCell.SetCellValue(intV);
                            break;
                        case "System.Decimal"://浮点型     
                        case "System.Double":
                        case "System.Single":
                            double doubV = 0;
                            double.TryParse(drValue, out doubV);
                            newCell.SetCellValue(doubV);
                            break;
                        case "System.DBNull"://空值处理     
                            newCell.SetCellValue("");
                            break;
                        default:
                            newCell.SetCellValue(drValue);
                            break;
                    }
                    count++;
                }
                #endregion

                rowIndex++;
            }

            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;
                //workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet     
                return ms;
            }
        }

        private static void AddFirstRow(HSSFWorkbook workbook, HSSFSheet sheet, Dictionary<string, string> FieldNames, string strHeaderText)
        {
            HSSFRow headerRow = (HSSFRow)sheet.CreateRow(0);
            headerRow.HeightInPoints = 25;
            headerRow.CreateCell(0).SetCellValue(strHeaderText);

            HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
            headStyle.Alignment = HorizontalAlignment.Center;
            HSSFFont font = (HSSFFont)workbook.CreateFont();
            font.FontHeightInPoints = 20;
            font.Boldweight = 700;
            headStyle.SetFont(font);
            headerRow.GetCell(0).CellStyle = headStyle;
            sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, FieldNames.Count - 1));
        }

        private static void AddSecondRow(HSSFWorkbook workbook, HSSFSheet sheet, Dictionary<string, string> FieldNames, int[] arrColWidth)
        {
            HSSFRow headerRow = (HSSFRow)sheet.CreateRow(1);
            HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
            headStyle.Alignment = HorizontalAlignment.Center;
            HSSFFont font = (HSSFFont)workbook.CreateFont();
            font.FontHeightInPoints = 10;
            font.Boldweight = 700;
            headStyle.SetFont(font);
            int count = 0; ;
            foreach (KeyValuePair<string, string> item in FieldNames)
            {
                //  arrColWidth[count] = Encoding.GetEncoding(936).GetBytes(item.Value).Length;  
                headerRow.CreateCell(count).SetCellValue(item.Value);
                headerRow.GetCell(count).CellStyle = headStyle;
                //设置列宽     
                sheet.SetColumnWidth(count, (arrColWidth[count] + 1) * 256);
                count++;
            }
        }
    }
}
